﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class PhieuNhapHangDAO
    {
        public List<PhieuNhapHangDTO> LaySoPhieuNhapHang()
        {
            string sql = "SELECT * FROM PhieuNhapHang WHERE Deleted = 0 ";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();
                List<PhieuNhapHangDTO> danhsach = new List<PhieuNhapHangDTO>();
                while (reader.Read())
                {
                    PhieuNhapHangDTO pnhDTO = new PhieuNhapHangDTO();

                    pnhDTO.SoPhieu = reader.GetString(0);
                    pnhDTO.MaHang = reader.GetString(1);
                    pnhDTO.NgayNhap = reader.GetDateTime(2);
                    pnhDTO.SoLuong = reader.GetInt32(3);
                    pnhDTO.DonGia = reader.GetInt32(4);
                    pnhDTO.ThueSuat = reader.GetInt32(5);
                    pnhDTO.ThanhTien = reader.GetInt32(6);
                    pnhDTO.MaHTTT = reader.GetInt32(7);
                    pnhDTO.NgayThanhToan = reader.GetDateTime(8);

                    danhsach.Add(pnhDTO);
                }
                conn.Close();
                return danhsach;
            }
        }
        public PhieuNhapHangDTO LayPhieuTheoMa(string SoPhieu)
        {
            string sql = "SELECT * FROM PhieuNhapHang WHERE SoPhieu = '" + SoPhieu + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();
                PhieuNhapHangDTO pnhDTO = new PhieuNhapHangDTO();
                reader.Read();
                    pnhDTO.SoPhieu = reader.GetString(0);
                    pnhDTO.MaHang = reader.GetString(1);
                    pnhDTO.NgayNhap = reader.GetDateTime(2);
                    pnhDTO.SoLuong = reader.GetInt32(3);
                    pnhDTO.DonGia = reader.GetInt32(4);
                    pnhDTO.ThueSuat = reader.GetInt32(5);
                    pnhDTO.ThanhTien = reader.GetInt32(6);
                    pnhDTO.MaHTTT = reader.GetInt32(7);
                    pnhDTO.NgayThanhToan = reader.GetDateTime(8);
                conn.Close();
                return pnhDTO;
            }
        }
        public bool XoaPhieuNhapHang(string SoPhieu)
        {
            string sql = "UPDATE PhieuNhapHang SET Deleted = 1 WHERE SoPhieu = '" + SoPhieu + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public bool ThemPhieuNhapHang(PhieuNhapHangDTO pnh)
        {
            string sql = "INSERT INTO PhieuNhapHang(SoPhieu, MaHang, NgayNhap, SoLuong, DonGia, ThueSuat, ThanhTien, MaHTTT, NgayThanhToan, Deleted)";
            sql += "VALUES('" + pnh.SoPhieu + "',N'" + pnh.MaHang + "', '" + pnh.NgayNhap + "', " + pnh.SoLuong + ", " + pnh.DonGia + ", " + pnh.ThueSuat + ", " + pnh.ThanhTien + ", " + pnh.MaHTTT + ", '" + pnh.NgayThanhToan + "', 0)";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public bool KTSoPhieu(string SoPhieu)
        {
            string sql = "SELECT COUNT(*) FROM PhieuNhapHang WHERE SoPhieu='" + SoPhieu + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                read.Read();
                int kq = read.GetInt32(0);
                conn.Close();
                if (kq == 0)
                    return true;
                return false;
            }

        }
        public bool SuaPhieuNhapHang(PhieuNhapHangDTO pnh)
        {
            string sql = "UPDATE PhieuNhapHang SET MaHang = '" + pnh.MaHang + "', NgayNhap = '" + pnh.NgayNhap + "', ";
            sql += "SoLuong = " + pnh.SoLuong + ", DonGia = " + pnh.DonGia + ", ThueSuat = " + pnh.ThueSuat + ", ThanhTien = " + pnh.ThanhTien + ",";
            sql += " MaHTTT = " + pnh.MaHTTT + ", NgayThanhToan = '" + pnh.NgayThanhToan + "' WHERE SoPhieu = '" + pnh.SoPhieu + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
    }
}
